import threading
import pymysql
from prettytable import PrettyTable
from PyQt5.QtCore import pyqtSignal
from PyQt5.QtGui import QIcon, QPixmap
from PyQt5.QtWidgets import QMainWindow, QMessageBox, QApplication
from PyQt5.uic import loadUiType
import waiting

ui, _ = loadUiType("./resource/ui/mysql.ui")


# 配置数据库界面
class Setup_Sql(QMainWindow, ui):
    sqlInfoEmit = pyqtSignal(list)

    def __init__(self):
        QMainWindow.__init__(self)
        self.setupUi(self)
        self.setWindowIcon(QIcon("./resource/icon/setup.png"))
        self.setWindowTitle("数据库配置")
        self.setFixedSize(self.width(), self.height())
        # 关键变量声明
        self.mysql_file = "./resource/mysql/mysql_setup.txt"
        self.database_index = 0
        self.mysql_message = []
        self.connect_flag = False
        self.connect_result = False
        # 等待连接界面
        self.waiting = waiting.wait_connect()
        # 清空文本框
        self.lineEdit.setText("")
        self.lineEdit_2.setText("")
        self.lineEdit_3.setText("")
        self.lineEdit_4.setText("")
        # 按钮图标
        self.pushButton.setIcon(QIcon(QPixmap("./resource/icon/look.png")))
        self.pushButton_2.setIcon(QIcon(QPixmap("./resource/icon/modification.png")))
        self.pushButton_3.setIcon(QIcon(QPixmap("./resource/icon/delete.png")))
        self.pushButton_4.setIcon(QIcon(QPixmap("./resource/icon/add.png")))
        self.pushButton_5.setIcon(QIcon(QPixmap("./resource/icon/test.png")))
        self.pushButton_6.setIcon(QIcon(QPixmap("./resource/icon/save.png")))
        self.pushButton_default.setIcon(QIcon(QPixmap("./resource/icon/default.png")))
        self.pushButton_setup.setIcon(QIcon(QPixmap("./resource/icon/set.png")))
        # 槽函数连接
        self.comboBox.currentIndexChanged.connect(self.choose_sql)
        self.pushButton.clicked.connect(self.look_sql)
        self.pushButton_2.clicked.connect(self.modification_sql)
        self.pushButton_3.clicked.connect(self.delete_sql)
        self.pushButton_4.clicked.connect(self.add_sql)
        self.pushButton_5.clicked.connect(self.test_sql)
        self.pushButton_6.clicked.connect(self.save_sql)
        self.pushButton_default.clicked.connect(self.default_sql)
        self.pushButton_setup.clicked.connect(self.setup_sql)
        # 加载数据库信息
        self.read_mysql_file()
        self.load_mysql()

    # 读取数据库配置文件
    def read_mysql_file(self):
        self.mysql_message = []
        try:
            with open(self.mysql_file, "r", encoding="utf-8") as f:
                data = f.readlines()
            for row, lines in enumerate(data):
                line = list(filter(lambda x: x != "|", lines.split()))
                if line[0] != 'host' and len(line) > 1:
                    self.mysql_message.append(line)
        except (IndexError, Exception):
            self.mysql_message.append(['127.0.0.1', '3306', 'root', 'lucky123'])

    # 更新数据库
    def update_mysql(self):
        tb = PrettyTable()
        tb.field_names = ["host", "port", "user", "password"]
        for i in range(len(self.mysql_message)):
            tb.add_row(self.mysql_message[i])
        with open(self.mysql_file, "w", encoding="utf-8") as f:
            f.writelines(str(tb) + "\n")

    # 加载数据库
    def load_mysql(self):
        self.comboBox.clear()
        for i in range(len(self.mysql_message)):
            self.comboBox.addItem("数据库" + str(i + 1))
        self.comboBox.setCurrentIndex(0)

    # 选择数据库
    def choose_sql(self):
        self.lineEdit.setText("")
        self.lineEdit_2.setText("")
        self.lineEdit_3.setText("")
        self.lineEdit_4.setText("")

    # 查看数据库信息
    def look_sql(self):
        # 显示当前数据库信息
        index = self.comboBox.currentIndex()
        message = self.mysql_message[index]
        self.lineEdit.setText(message[0])
        self.lineEdit_2.setText(str(message[1]))
        self.lineEdit_3.setText(message[2])
        self.lineEdit_4.setText(message[3])
        # 设置为不可编辑
        self.lineEdit.setEnabled(False)
        self.lineEdit_2.setEnabled(False)
        self.lineEdit_3.setEnabled(False)
        self.lineEdit_4.setEnabled(False)

    # 修改数据库信息
    def modification_sql(self):
        self.look_sql()
        # 设置为可编辑
        self.lineEdit.setEnabled(True)
        self.lineEdit_2.setEnabled(True)
        self.lineEdit_3.setEnabled(True)
        self.lineEdit_4.setEnabled(True)

    # 删除数据库信息
    def delete_sql(self):
        index = self.comboBox.currentIndex()
        reply = QMessageBox.warning(self, "警告", "删除当前数据库", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
        if reply == QMessageBox.Yes:
            self.mysql_message.pop(index)
            self.load_mysql()
            self.update_mysql()

    # 新增数据库信息
    def add_sql(self):
        index = len(self.mysql_message)
        self.mysql_message.append([])
        self.comboBox.addItem("数据库" + str(index + 1))
        self.comboBox.setCurrentIndex(index)
        # 清空数据
        self.lineEdit.setText("")
        self.lineEdit_2.setText("")
        self.lineEdit_3.setText("")
        self.lineEdit_4.setText("")
        # 设置为可编辑
        self.lineEdit.setEnabled(True)
        self.lineEdit_2.setEnabled(True)
        self.lineEdit_3.setEnabled(True)
        self.lineEdit_4.setEnabled(True)

    # 数据库连接测试结果
    def sql_test_result(self, info):
        try:
            pymysql.connect(host=info[0], port=int(info[1]), user=info[2], password=info[3])
            self.connect_flag = True
            self.connect_result = True
        except (IndexError, Exception):
            self.connect_flag = True
            self.connect_result = False

    # 测试数据库信息
    def test_sql(self):
        self.waiting.show()
        try:
            host = self.lineEdit.text()
            port = int(self.lineEdit_2.text())
            user = self.lineEdit_3.text()
            password = self.lineEdit_4.text()
            if host and port and user and password:
                info = [host, port, user, password]
                sql_test = threading.Thread(target=self.sql_test_result, args=(info,))
                sql_test.daemon = 1
                sql_test.start()
                while not self.connect_flag:
                    QApplication.processEvents()
                    if self.connect_flag:
                        if self.connect_result:
                            for i in range(80000):
                                QApplication.processEvents()
                            self.connect_flag = False
                            self.connect_result = False
                            QMessageBox.about(self, "提示", "连接成功")
                            self.waiting.close()
                            break
                        else:
                            self.connect_flag = False
                            self.connect_result = False
                            QMessageBox.warning(self, "警告", "连接失败")
                            self.waiting.close()
                            break
            else:
                QMessageBox.about(self, "提示", "请输入正确的数据库信息")
                self.waiting.close()
        except (IndexError, Exception):
            QMessageBox.warning(self, "警告", "建立数据库连接测试失败")
            self.waiting.close()

    # 保存数据库信息
    def save_sql(self):
        index = self.comboBox.currentIndex()
        try:
            host = self.lineEdit.text()
            port = int(self.lineEdit_2.text())
            user = self.lineEdit_3.text()
            password = self.lineEdit_4.text()
            if host and port and user and password:
                info = [host, port, user, password]
                # 修改数据库
                if len(self.mysql_message) > index:
                    self.mysql_message[index] = info
                # 增加数据库
                else:
                    self.mysql_message.append(info)
                QMessageBox.about(self, "提示", "保存成功")
                self.look_sql()
                self.update_mysql()
            else:
                QMessageBox.warning(self, "警告", "保存失败，请先输入正确的数据库信息")
        except (IndexError, Exception):
            QMessageBox.warning(self, "警告", "保存失败，请先输入完整的数据库信息")

    # 默认数据库设置
    def default_sql(self):
        QMessageBox.about(self, "提示", "加载默认数据库设置")
        self.comboBox.setCurrentIndex(0)
        self.look_sql()

    # 完成当前数据库设置
    def setup_sql(self):
        self.update_mysql()
        self.database_index = self.comboBox.currentIndex()
        self.sqlInfoEmit.emit(self.mysql_message[self.database_index])
        QMessageBox.about(self, '提示', '数据库配置成功')
